package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.*;
import cn.edu.sdjzu.xg.bysj.service.GraduateProjectStatusService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public  final class GraduateProjectDao {
    private static Collection<GraduateProject> graduateProjects;
    private static GraduateProjectDao getGraduateProjectDao = null;

    private GraduateProjectDao() {

    }

    public static GraduateProjectDao getInstance() {
        if (getGraduateProjectDao == null) {
            getGraduateProjectDao = new GraduateProjectDao();
        }
        return getGraduateProjectDao;
    }

    public int count(Connection connection) throws SQLException {
        return CommonDao.getInstance().count(connection,"GraduateProject");
    }

    public Collection<GraduateProject> findAll(List conditionList,
                                               Pagination pagination,
                                               Connection connection)
            throws SQLException{
        //创建集合类对象，用来保存所有记录代表的GraduateProject对象
        Collection<GraduateProject> graduateProjects = new TreeSet<GraduateProject>();
        //准备预编译的语句
        int totalNum = GraduateProjectDao.getInstance().count(connection);
        StringBuilder sql_select = new StringBuilder("SELECT * FROM GraduateProject ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while(resultSet.next()){
            graduateProjects.add(
                    new GraduateProject(
                            resultSet.getInt("id"),
                            resultSet.getString("title")
                    )
            );
        }
        JdbcHelper.close(resultSet,pstmt);
        return graduateProjects;
    }
    public static GraduateProject find(int id,Connection connection)
            throws SQLException{
        //创建sql语句
        String find_sql = "select * from GraduateProject where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //创建结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        GraduateProject graduateProject = null;
        //游标下移
        if(resultSet.next()) {
            //创建GraduateProject对象，各字段为结果集中相应字段
            graduateProject = new GraduateProject(
                    resultSet.getInt("id"),
                    resultSet.getString("title")
            );
        }
        JdbcHelper.close(resultSet,pstmt);
        return graduateProject;
    }

    public boolean add(GraduateProject graduateProject,Connection connection)
            throws SQLException{
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement pstmt =
                connection.prepareStatement(
                        "INSERT INTO GraduateProject ( " +
                                "title," +
                                "graduateProjectCategory_id," +
                                "graduateProjectType_id," +
                                "graduateProjectStatus_id," +
                                "teacher_id)" +
                                " VALUES (?,?,?,?,?)");
        pstmt.setString(1,graduateProject.getTitle());
        pstmt.setInt(2,graduateProject.getGraduateProjectCategory().getId());
        pstmt.setInt(3,graduateProject.getGraduateProjectType().getId());
        pstmt.setInt(4,graduateProject.getGraduateProjectStatus().getId());
        pstmt.setInt(5,graduateProject.getTeacher().getId());
        int totalNum = pstmt.executeUpdate();
        //本方法是事务的一部分，不能释放资源
        JdbcHelper.close(pstmt,null);
        return totalNum>0;
    }

    public boolean update(GraduateProject graduateProject,Connection connection)
            throws SQLException{
        //创建sql语句
        String updateSchool_sql = "update GraduateProject set " +
                "title=?," +
                "graduateProjectCategory_id=?," +
                "graduateProjectType_id = ?," +
                "graduateProjectStatus_id=?," +
                "teacher_id=? " +
                "where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateSchool_sql);
        //为预编译参数赋值
        pstmt.setString(1,graduateProject.getTitle());
        pstmt.setInt(2,graduateProject.getGraduateProjectCategory().getId());
        pstmt.setInt(3,graduateProject.getGraduateProjectType().getId());
        pstmt.setInt(4,graduateProject.getGraduateProjectStatus().getId());
        pstmt.setInt(5,graduateProject.getTeacher().getId());
        pstmt.setInt(6,graduateProject.getId());

        int totalNum = pstmt.executeUpdate();
        JdbcHelper.close(pstmt,null);

        return  totalNum>0;
    }

    public boolean delete(int id,Connection connection) throws SQLException {
        //创建sql语句
        String deleteGraduateProject_sql = "DELETE FROM GraduateProject WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteGraduateProject_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);

        int totalNum = pstmt.executeUpdate();
        JdbcHelper.close(pstmt,null);

        return  totalNum>0;
    }
}


//        try{
//            Department misDepartment = DepartmentDao.getInstance().find(2);
//
//            Major misMajor = MajorDao.getInstance().find(1);
//            Major ieMajor = MajorDao.getInstance().find(2);
//
//            Teacher st = TeacherDao.getInstance().find(1);
//            Teacher lx = TeacherDao.getInstance().find(2);
//            Teacher wf = TeacherDao.getInstance().find(3);
//
//            GraduateProjectStatus applied = GraduateProjectStatusService.getInstance().find(1);
//
//            GraduateProjectCategory design = GraduateProjectCategoryDao.getInstance()
//                    .find(1);
//            GraduateProjectCategory thesis = GraduateProjectCategoryDao.getInstance()
//                    .find(2);
//
//            GraduateProjectType real = GraduateProjectTypeDao.getInstance().find(1);
//            GraduateProjectType virtual = GraduateProjectTypeDao.getInstance().find(2);
//
//            GraduateProject project1 = new GraduateProject("虚拟现实中碰撞处理的研究",
//                    thesis, virtual, applied, st);
//            GraduateProject project2 = new GraduateProject("某高校监考管理系统的设计与实现",
//                    design, real, applied, st);
//            GraduateProject project3 = new GraduateProject("网上教学平台的设计与实现",
//                    design, real, applied, lx);
//            GraduateProject project4 = new GraduateProject("基于分形技术的防伪研究",
//                    thesis, real, applied, wf);
//
//            projects = new TreeSet<GraduateProject>();
//            projects.add(project1);
//            projects.add(project2);
//            projects.add(project3);
//            projects.add(project4);
//        }catch (Exception e){
//            e.printStackTrace();
//        }



